package webpage;

import java.sql.*;
import java.util.ArrayList;

import javax.sql.*;

import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
import com.sun.net.httpserver.HttpServer;

public class CreateDBConnection {

	String dbtime;
	String dbUrl = "jdbc:mysql://localhost:3306/test";
	String dbClass = "com.mysql.jdbc.Driver";

	public boolean verifyLogin(String emailaddress, String password) {
		String query = "select password from welcome_cred where email_address= '"
				+ emailaddress + "'";
		boolean result = false;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(dbUrl, "root",
					"ranjeet");
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			// System.out.println(rs.getString(1));
			while (rs.next()) {
				dbtime = rs.getString(1);
			}
			if (dbtime.equals(password)) {
				result = true;
			}
			stmt.close();
			con.close();
			// end while

		} // end try

		catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		}

		catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public String register(String first_name, String last_name, String sex,
			String country, String emailaddress, String password, String profilepic) {
		String query = "insert into welcome_cred (first_name,last_name, sex,country,email_address,password,profile_pic,email_valid) values ('"
				+ first_name
				+ "','"
				+ last_name
				+ "','"
				+ sex
				+ "','"
				+ country + "','" + emailaddress + "','" + password + "','"+profilepic+"','N')";
		String message = "Registration Successful";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(dbUrl, "root",
					"ranjeet");
			Statement stmt = con.createStatement();
			stmt.executeUpdate(query);
			con.close();

		} // end try

		catch (ClassNotFoundException ex) {
			message = "Class not found";
			ex.printStackTrace();
		}

		catch (SQLIntegrityConstraintViolationException ez) {
			message = emailaddress + " already exists";
			ez.printStackTrace();
		} catch (SQLException e) {
			message = "SQL Exception";
			e.printStackTrace();
		}

		finally {
			return message;
		}
	}

	public ArrayList<String> getDetails(String emailaddress) {
		String query = "select * from welcome_cred where email_address='"
				+ emailaddress + "'";
		ArrayList<String> details = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(dbUrl, "root",
					"ranjeet");
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			int i = 1;
			int j=0;
			while (rs.next()) {
				while(rs.last()){
					details.add(j, rs.getString(i));
					i++;
					j++;
				}
			}
			stmt.close();
			con.close();
			// end while

		} // end try

		catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		}

		catch (SQLException e) {
			e.printStackTrace();
		}
		return details;
	}
	public String updateColumn(String columnName, String value, String emailaddress){
		String query = "update welcome_cred set "+columnName+"='"+value+"'"+" where email_address='"+emailaddress+"'";
		String result = "Success";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(dbUrl, "root",
					"ranjeet");
			Statement stmt = con.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			// System.out.println(rs.getString(1));
			
			con.close();
			

		} // end try

		catch (ClassNotFoundException ex) {
			ex.printStackTrace();
			result="Class not found";
		}

		catch (SQLException e) {
			e.printStackTrace();
			result="SQL Exception";
		}
		return result;
	}

	public ArrayList<String> searchUser(String search) {
		String query = "select email_address from welcome_cred where email_address like '%"+ search + "%'";
		ArrayList<String> searchResult = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(dbUrl, "root",
					"ranjeet");
			//System.out.println(query);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			int j=0;
				while (rs.next()) {
						System.out.println(rs.getString(1));
						searchResult.add(j, rs.getString(1));
						j++;
					}
		
		
			
			con.close();
			// end while

		} // end try

		catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		}

		catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(searchResult.size());
		return searchResult;
	}
	public String searchColumn(String selectColumn, String parameterValue, String Parameter){
		String query = "select "+selectColumn+ " from welcome_cred where "+Parameter+"='"+parameterValue+"'";
		String result = "No matching results";
		System.out.println(query);
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(dbUrl, "root",
					"ranjeet");
			Statement stmt = con.createStatement();
			ResultSet rs=stmt.executeQuery(query);
			while (rs.next()) {
				result = rs.getString(1);
			}
			stmt.close();
			// System.out.println(rs.getString(1));
			
			con.close();
			

		} // end try

		catch (ClassNotFoundException ex) {
			ex.printStackTrace();
			//result="Class not found";
		}

		catch (SQLException e) {
			e.printStackTrace();
			//result="SQL Exception";
		}
		return result;
	}

	
}
